#include "stusql.h"
#include<QDebug>
#include<QTextCodec>
stuSql* stuSql::ptrstuSql=new stuSql;
stuSql::stuSql(QObject *parent) : QObject(parent)
{
    //init();
//    StuInfo s;
//    s.name="cjs";
//    s.id=1;
    //addStu(s);
    //getStuCnt();
//    QList<StuInfo> info=getPageStu(3,2);
//    for(QList<StuInfo>::iterator it=info.begin();it!=info.end();it++){
//        qDebug()<<(*it).id<<","<<it->name;
//    }
    //delStu(5);
   // updateStuInfo(s);
//    QList<UserInfo> info=getAllUser();
//    for(QList<UserInfo>::iterator it=info.begin();it!=info.end();it++){
//            qDebug()<<(*it).username<<","<<(*it).password<<","<<(*it).author;
//        }
    //qDebug()<<isExist("ltc");
//    UserInfo u;
//    u.username="phw";
//    u.password="123";
//    u.author="phw";
//    addUser(u);
//     UserInfo u;
//     u.password="123456";
//     u.username="phw";
//    updateUser(u);

//    init();
//    QList<StuInfo> info=getAllStu();
//    for(QList<StuInfo>::iterator it=info.begin();it!=info.end();it++){
//        qDebug()<<(*it).id<<","<<(*it).name<<","<<(*it).age;
//   }

//    StuInfo info;
//    info.name="ggg";
//    info.id=15;
//    updateStuInfo(info);
}

quint32 stuSql::getStuCnt()
{
    QSqlQuery sql;
    sql.exec("select count(id) from student");
    quint32 uiCnt=0;
    while (sql.next()) {
        uiCnt = sql.value(0).toUInt();
    }
    return uiCnt;
}

QList<StuInfo> stuSql::getPageStu(quint32 page, quint32 uicnt)
{
    QList<StuInfo> l;
    QSqlQuery sql;
    QString strSql=QString("select * from student order by id limit %1,%2")
            .arg((page-1)*uicnt)
            .arg(uicnt);
    sql.exec(strSql);
    StuInfo info;
    while (sql.next()) {
        info.id = sql.value(0).toUInt();
        info.name = sql.value(1).toString();
        info.age = sql.value(2).toUInt();
        info.grade = sql.value(3).toUInt();
        info.uiclass = sql.value(4).toUInt();
        info.studentid = sql.value(5).toUInt();
        info.phone = sql.value(6).toString();
        info.wechat = sql.value(7).toString();
        l.push_back(info);
    }
    return l;
}

bool stuSql::addStu(StuInfo info)
{
    QSqlQuery sql;
    QString strSql=QString("insert into student values(null,'%1',%2,%3,%4,%5,'%6','%7')")
            .arg(info.name)
            .arg(info.age)
            .arg(info.grade)
            .arg(info.uiclass)
            .arg(info.studentid)
            .arg(info.phone)
            .arg(info.wechat);
    return sql.exec(strSql);
}

bool stuSql::addStu(QList<StuInfo> l)
{
    QSqlQuery sql;
    //添加事务后执行速度变快
    db.transaction();//开启事务
    for(auto info:l){
        QString strSql=QString("insert into student values(null,'%1',%2,%3,%4,%5,'%6','%7')")
                .arg(info.name)
                .arg(info.age)
                .arg(info.grade)
                .arg(info.uiclass)
                .arg(info.studentid)
                .arg(info.phone)
                .arg(info.wechat);
        sql.exec(strSql);
    }
    db.commit();
    return true;
}

bool stuSql::delStu(int id)
{
    QSqlQuery sql;
    QString strSql=QString("delete from student where id=%1")
            .arg(id);
    return sql.exec(strSql);
}

bool stuSql::clearStuTable()
{
    QSqlQuery sql;
    QString strSql=QString("drop from student");
    sql.exec(strSql);
    quint32 n=this->getStuCnt();
    //删除表之后重写设置主键自增
     QString strSql2=QString("ALTER TABLE student auto_increment=%1").arg(n);
    return sql.exec(strSql2);
}

bool stuSql::updateStuInfo(StuInfo info)
{
    QSqlQuery sql;
    QString strSql=QString("update student set name = '%1', age=%2 ,grade=%3, class=%4, studentid=%5 ,phone='%6', wechat='%7' where id = %8")
            .arg(info.name)
            .arg(info.age)
            .arg(info.grade)
            .arg(info.uiclass)
            .arg(info.studentid)
            .arg(info.phone)
            .arg(info.wechat)
            .arg(info.id);
    return sql.exec(strSql);
}

QList<StuInfo> stuSql::getAllStu()
{
    QList<StuInfo> l;
    QSqlQuery sql;
    sql.exec("select * from student");
    StuInfo info;
    while(sql.next()){
        info.id = sql.value(0).toUInt();
        info.name = sql.value(1).toString();
        info.age = sql.value(2).toUInt();
        info.grade = sql.value(3).toUInt();
        info.uiclass = sql.value(4).toUInt();
        info.studentid = sql.value(5).toUInt();
        info.phone = sql.value(6).toString();
        info.wechat = sql.value(7).toString();
        l.push_back(info);
    }
    return l;
}

QList<StuInfo> stuSql::getStuByName(QString name)
{
    QList<StuInfo> l;
    StuInfo info;
    QSqlQuery sql;
    QString strSql=QString("select * from student where name like '%%1%'")
            .arg(name);
     sql.exec(strSql);
     while(sql.next()){
         info.id = sql.value(0).toUInt();
         info.name = sql.value(1).toString();
         info.age = sql.value(2).toUInt();
         info.grade = sql.value(3).toUInt();
         info.uiclass = sql.value(4).toUInt();
         info.studentid = sql.value(5).toUInt();
         info.phone = sql.value(6).toString();
         info.wechat = sql.value(7).toString();
         l.push_back(info);
     }
    return l;
}

QList<UserInfo> stuSql::getAllUser()
{
    QList<UserInfo> l;
    QSqlQuery sql;
    sql.exec("select * from username");
    UserInfo userInfo;
    while(sql.next()){
        userInfo.username=sql.value(0).toString();
        userInfo.password=sql.value(1).toString();
        userInfo.author=sql.value(2).toString();
        l.push_back(userInfo);
    }
    return l;
}

bool stuSql::isExist(QString strUser)
{
    QSqlQuery sql;
    sql.exec(QString("select * from username where username = '%1'").arg(strUser));
    return sql.next();
}

bool stuSql::updateUser(UserInfo info)
{
    QSqlQuery sql;
    QString strSql=QString("update username set password='%1' ,author='%2' where username = '%3'")
            .arg(info.password)
            .arg(info.author)
            .arg(info.username);
    return sql.exec(strSql);
}

bool stuSql::addUser(UserInfo info)
{
    QSqlQuery sql;
    QString strSql=QString("insert into username values('%1','%2','%3')")
            .arg(info.username)
            .arg(info.password)
            .arg(info.author);
    return sql.exec(strSql);
}

bool stuSql::delUser(QString strUserName)
{
    QSqlQuery sql;
    return sql.exec(QString("delete from username where username = '%1'").arg(strUserName));
}

bool stuSql::login(QString username, QString password)
{
    QSqlQuery sql;
    QString strSql=QString("select * from username where username='%1' and password='%2'")
            .arg(username).arg(password);
    sql.exec(strSql);
    return sql.first();

}

void stuSql::init()
{
    //添加mysql数据库
   db =QSqlDatabase::addDatabase("QMYSQL");
   //连接数据库
   db.setHostName("localhost");
   db.setUserName("root");
   db.setPassword("123");
   db.setDatabaseName("data");//连接那个数据库
   //打开数据库
   if(!db.open()){//数据库打开失败
       QMessageBox::warning(nullptr,"错误",db.lastError().text());
        return;
   }
}


